﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace QuerySyntaxSample
{
    public class CustomePerson
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int Age { get; set; }
    } 

    class Program
    {
        static void Main(string[] args)
        {
            /////////////////////////////////////////////
            // Simple Query syntax for object
            ////////////////////////////////////////////            
            List<CustomePerson> people = new List<CustomePerson> 
            {
                new CustomePerson { FirstName = "Scott", LastName = "Guthrie", Age = 32 },
                new CustomePerson { FirstName = "Bill", LastName = "Gates", Age = 50 },
                new CustomePerson { FirstName = "Susanne", LastName = "Guthrie", Age = 32 }
            };

            // Query syntax 
            IEnumerable<CustomePerson> result = from p in people
                                                where p.FirstName.StartsWith("S")
                                                orderby p.LastName
                                                select p;
            
            // Query syntax equal to LINQ syntax
            IEnumerable<CustomePerson> result2 = people.Where(p => p.FirstName.StartsWith("S")).OrderBy(p => p.LastName);

            /////////////////////////////////////////////
            // Query syntax for DB
            ////////////////////////////////////////////
            NorthwindDataContext db = new NorthwindDataContext();

            // C# 编译器会把我们的声明式查询句法翻译成明确的扩展方法调用（使用Lambda表达式作为参数)
            IEnumerable<Product> products = from p in db.Products
                                            where p.UnitPrice > 99               
                                            select p;

            IEnumerable<string> categories = from c in db.Categories
                                             where c.Products.Count > 5
                                             orderby c.CategoryName descending
                                             select c.CategoryName;

            // LINQ to SQL, Ultimately it will use
            
                //SELECT [t0].[CategoryName] FROM [dbo].[Categories] AS [t0]
                //WHERE ((
                //    SELECT COUNT(*)
                //    FROM [dbo].[Products] AS [t1]
                //    WHERE [t1].[CategoryID] = [t0].[CategoryID]
                //)) > 5
                //ORDER BY [t0].[CategoryName] DESC

            // Deferred Execution: 
            // acutally the search will be run when you do 
            //   foreach/ToList/ToArray
        }
    }
}
